SQL Correlated Subquery: Correlated Subquery in SQL By Examples

2024-07-12 10:33| 来源: 网络整理| 查看: 265

Summary: in this tutorial, you will learn about the SQL correlated subquery which is a subquery that uses values from the outer query.

Introduction to SQL correlated subquery

Let’s start with an example.

See the following employees table in the sample database:

SQL Correlated Subquery: Employees Table

The following query finds employees whose salary is greater than the average salary of all employees:

SELECT employee_id, first_name, last_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); Code language: SQL (Structured Query Language) (sql) SQL Subquery example

In this example, the subquery is used in the WHERE clause. There are some points that you can see from this query:

First, you can execute the subquery that returns the average salary of all employees independently.

SELECT AVG(salary) FROM employees; Code language: SQL (Structured Query Language) (sql)

Second, the database system needs to evaluate the subquery only once.

Third, the outer query makes use of the result returned from the subquery. The outer query depends on the subquery for its value. However, the subquery does not depend on the outer query. Sometimes, we call this subquery is a plain subquery.

Unlike a plain subquery, a correlated subquery is a subquery that uses the values from the outer query. Also, a correlated subquery may be evaluated once for each row selected by the outer query. Because of this, a query that uses a correlated subquery may be slow.

A correlated subquery is also known as a repeating subquery or a synchronized subquery.

SQL correlated subquery examples

Let’s see few more examples of the correlated subqueries to understand them better.

SQL correlated subquery in the WHERE clause example

The following query finds all employees whose salary is higher than the average salary of the employees in their departments:

SELECT employee_id, first_name, last_name, salary, department_id FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) ORDER BY department_id , first_name , last_name; Code language: SQL (Structured Query Language) (sql)

Here is the output:

In this example, the outer query is:

SELECT employee_id, first_name, last_name, salary, department_id FROM employees e WHERE salary > ... Code language: SQL (Structured Query Language) (sql)

and the correlated subquery is:

SELECT AVG( list_price ) FROM products WHERE category_id = p.category_id Code language: SQL (Structured Query Language) (sql)

For each employee, the database system has to execute the correlated subquery once to calculate the average salary of the employees in the department of the current employee.

SQL correlated subquery in the SELECT clause example

The following query returns the employees and the average salary of all employees in their departments:

SELECT employee_id, first_name, last_name, department_name, salary, (SELECT ROUND(AVG(salary),0) FROM employees WHERE department_id = e.department_id) avg_salary_in_department FROM employees e INNER JOIN departments d ON d.department_id = e.department_id ORDER BY department_name, first_name, last_name; Code language: SQL (Structured Query Language) (sql)

The output is:

SQL Correlated Subquery in SELECT clause

For each employee, the database system has to execute the correlated subquery once to calculate the average salary by the employee’s department.

SQL correlated subquery with EXISTS operator example

We often use a correlated subquery with the EXISTS operator. For example, the following query returns all employees who have no dependents:

SELECT employee_id, first_name, last_name FROM employees e WHERE NOT EXISTS( SELECT * FROM dependents d WHERE d.employee_id = e.employee_id) ORDER BY first_name , last_name; Code language: SQL (Structured Query Language) (sql)

The following picture shows the output:

SQL Correlated Subquery with EXISTS operator

In this tutorial, you have learned about the SQL correlated subquery and how to apply it to form a complex query.

Was this tutorial helpful ?

【本文地址】

公司简介

联系我们